﻿using Model;
using System;
using System.Data;

namespace BLL
{
    public class YX_TradeBillBLL : BaseBLL
    {
        public DataTable GetListByPage(string whereStr, string orderby, int startIndex, int endIndex)
        {
            string sql = string.Format(@"SELECT r,
       N_TRADEBILL_ID,
       CMY_NAME,
       C_NAME,
       C_TRADEBILL_DRAWERACCOUNT,
       USER_NAME,
       C_TRADEBILL_PAYERNAME,
       C_TRADEBILL_PAYEENAME,
       C_TRADEBILL_PAYEECMYNAME,
       C_TRADEBILL_PAYEEBANKNAME,
       C_TRADEBILL_PAYEEACCOUNT,
       N_TRADEBILL_INVOICEVALUE,
       to_char(D_TRADEBILL_STARTDATE, 'yyyy-mm-dd') D_TRADEBILL_STARTDATE,
       to_char(D_TRADEBILL_ENDDATE, 'yyyy-mm-dd') D_TRADEBILL_ENDDATE,
       C_TRADEBILL_CODE,
       N_TRADEBILL_IFOFFER,
       C_TRADEBILL_REMARK
  FROM (SELECT ROW_NUMBER() OVER(ORDER BY " + orderby + @") as r,
       YXTB.N_TRADEBILL_ID            AS N_TRADEBILL_ID,
       SCOM.CMY_NAME                  AS CMY_NAME,
       SBAN.C_NAME                    AS C_NAME,
       YXTB.C_TRADEBILL_DRAWERACCOUNT AS C_TRADEBILL_DRAWERACCOUNT,
       SUSER.USER_NAME                AS USER_NAME,
       YXTB.C_TRADEBILL_PAYERNAME     AS C_TRADEBILL_PAYERNAME,
       YXTB.C_TRADEBILL_PAYEENAME     AS C_TRADEBILL_PAYEENAME,
       YXTB.C_TRADEBILL_PAYEECMYNAME  AS C_TRADEBILL_PAYEECMYNAME,
       YXTB.C_TRADEBILL_PAYEEBANKNAME AS C_TRADEBILL_PAYEEBANKNAME,
       YXTB.C_TRADEBILL_PAYEEACCOUNT  AS C_TRADEBILL_PAYEEACCOUNT,
       YXTB.N_TRADEBILL_INVOICEVALUE  AS N_TRADEBILL_INVOICEVALUE,
       YXTB.D_TRADEBILL_STARTDATE     AS D_TRADEBILL_STARTDATE,
       YXTB.D_TRADEBILL_ENDDATE       AS D_TRADEBILL_ENDDATE,
       YXTB.C_TRADEBILL_CODE          AS C_TRADEBILL_CODE,
       YXTB.N_TRADEBILL_IFOFFER       AS N_TRADEBILL_IFOFFER,
       YXTB.C_TRADEBILL_REMARK        AS C_TRADEBILL_REMARK
  FROM YX_TRADEBILL YXTB
  LEFT JOIN YX_ACCOUNT YXCT
    ON YXTB.C_TRADEBILL_DRAWERACCOUNT = YXCT.ACCOUNTNO
  LEFT JOIN SYS_COMPANY SCOM
    ON YXTB.N_TRADEBILL_DRAWERCMYID = SCOM.CMY_ID
  LEFT JOIN SYS_BANK SBAN
    ON YXTB.N_TRADEBILL_DRAWERBANKID = SBAN.N_ID
  LEFT JOIN SYS_USER SUSER
    ON YXTB.N_TRADEBILL_DRAWERID = SUSER.USER_ID
         WHERE YXCT.ACCOUNTTYPE = 1
              --AND YXTB.N_TRADEBILL_DRAWERCMYID = YXCT.COMPANY_ID
              --AND YXTB.N_TRADEBILL_DRAWERBANKID = YXCT.N_BANK_ID
           /*AND SUSER.USER_COMPANY = SCOM.CMY_ID
              AND SUSER.USER_COMPANY = YXCT.COMPANY_ID
              AND SUSER.USER_DEPARTMENT = YXCT.DEPARTMENT_ID*/ ");

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }

            sql += @" ) TT";
            if (startIndex >= 2)
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + "+1 AND " + startIndex + "*" + endIndex + "";
            }
            else
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + " AND " + startIndex + "*" + endIndex + "";
            }
            return Query(sql, "", "GetListByPage", "查询队列", "");
        }

        public int GetRecordCount(string whereStr)
        {
            //          string sql = string.Format(@"SELECT count(1)
            //        FROM YX_TRADEBILL YXTB
            //LEFT JOIN YX_ACCOUNT YXCT
            //  ON YXTB.C_TRADEBILL_DRAWERACCOUNT = YXCT.ACCOUNTNO
            //LEFT JOIN SYS_COMPANY SCOM
            //  ON YXTB.N_TRADEBILL_DRAWERCMYID = SCOM.CMY_ID
            //LEFT JOIN SYS_BANK SBAN
            //  ON YXTB.N_TRADEBILL_DRAWERBANKID = SBAN.N_ID
            //LEFT JOIN SYS_USER SUSER
            //  ON YXTB.N_TRADEBILL_DRAWERID = SUSER.USER_ID
            //       WHERE YXCT.ACCOUNTTYPE = 1
            //            --AND YXTB.N_TRADEBILL_DRAWERCMYID = YXCT.COMPANY_ID
            //         AND YXTB.N_TRADEBILL_DRAWERBANKID = YXCT.N_BANK_ID
            //         AND SUSER.USER_COMPANY = SCOM.CMY_ID
            //            /*AND SUSER.USER_COMPANY = YXCT.COMPANY_ID
            //            AND SUSER.USER_DEPARTMENT = YXCT.DEPARTMENT_ID*/ " + whereStr);

            string sql = string.Format(@"SELECT COUNT(*)
  FROM (SELECT
       YXTB.N_TRADEBILL_ID            AS N_TRADEBILL_ID,
       SCOM.CMY_NAME                  AS CMY_NAME,
       SBAN.C_NAME                    AS C_NAME,
       YXTB.C_TRADEBILL_DRAWERACCOUNT AS C_TRADEBILL_DRAWERACCOUNT,
       SUSER.USER_NAME                AS USER_NAME,
       YXTB.C_TRADEBILL_PAYERNAME     AS C_TRADEBILL_PAYERNAME,
       YXTB.C_TRADEBILL_PAYEENAME     AS C_TRADEBILL_PAYEENAME,
       YXTB.C_TRADEBILL_PAYEECMYNAME  AS C_TRADEBILL_PAYEECMYNAME,
       YXTB.C_TRADEBILL_PAYEEBANKNAME AS C_TRADEBILL_PAYEEBANKNAME,
       YXTB.C_TRADEBILL_PAYEEACCOUNT  AS C_TRADEBILL_PAYEEACCOUNT,
       YXTB.N_TRADEBILL_INVOICEVALUE  AS N_TRADEBILL_INVOICEVALUE,
       YXTB.D_TRADEBILL_STARTDATE     AS D_TRADEBILL_STARTDATE,
       YXTB.D_TRADEBILL_ENDDATE       AS D_TRADEBILL_ENDDATE,
       YXTB.C_TRADEBILL_CODE          AS C_TRADEBILL_CODE,
       YXTB.N_TRADEBILL_IFOFFER       AS N_TRADEBILL_IFOFFER,
       YXTB.C_TRADEBILL_REMARK        AS C_TRADEBILL_REMARK
  FROM YX_TRADEBILL YXTB
  LEFT JOIN YX_ACCOUNT YXCT
    ON YXTB.C_TRADEBILL_DRAWERACCOUNT = YXCT.ACCOUNTNO
  LEFT JOIN SYS_COMPANY SCOM
    ON YXTB.N_TRADEBILL_DRAWERCMYID = SCOM.CMY_ID
  LEFT JOIN SYS_BANK SBAN
    ON YXTB.N_TRADEBILL_DRAWERBANKID = SBAN.N_ID
  LEFT JOIN SYS_USER SUSER
    ON YXTB.N_TRADEBILL_DRAWERID = SUSER.USER_ID
         WHERE YXCT.ACCOUNTTYPE = 1
              --AND YXTB.N_TRADEBILL_DRAWERCMYID = YXCT.COMPANY_ID
              --AND YXTB.N_TRADEBILL_DRAWERBANKID = YXCT.N_BANK_ID
           AND SUSER.USER_COMPANY = SCOM.CMY_ID
              /*AND SUSER.USER_COMPANY = YXCT.COMPANY_ID
              AND SUSER.USER_DEPARTMENT = YXCT.DEPARTMENT_ID*/ ");

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }

            sql += @" ) TT";

            int count = GetCount(sql);

            return count;
        }

        /// <summary>
        /// 根据编号查询银行汇票明细
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public YX_TradeBill SelectById(string id)
        {
            string sql = string.Format(@"select * from YX_TradeBill a where a.N_TRADEBILL_ID= " + id);

            DataTable dt = Query(sql);

            YX_TradeBill model = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    model = new YX_TradeBill();
                    model.N_TRADEBILL_ID = Convert.ToDecimal(id);
                    model.N_TRADEBILL_DRAWERCMYID = decimal.Parse(dr["N_TRADEBILL_DRAWERCMYID"].ToString());
                    model.N_TRADEBILL_DRAWERBANKID = decimal.Parse(dr["N_TRADEBILL_DRAWERBANKID"].ToString());
                    model.C_TRADEBILL_DRAWERACCOUNT = dr["C_TRADEBILL_DRAWERACCOUNT"].ToString();
                    model.N_TRADEBILL_DRAWERID = decimal.Parse(dr["N_TRADEBILL_DRAWERID"].ToString());
                    model.C_TRADEBILL_PAYERNAME = dr["C_TRADEBILL_PAYERNAME"].ToString();
                    model.C_TRADEBILL_PAYEENAME = dr["C_TRADEBILL_PAYEENAME"].ToString();
                    model.C_TRADEBILL_PAYEECMYNAME = dr["C_TRADEBILL_PAYEECMYNAME"].ToString();
                    model.C_TRADEBILL_PAYEEBANKNAME = dr["C_TRADEBILL_PAYEEBANKNAME"].ToString();
                    model.C_TRADEBILL_PAYEEACCOUNT = dr["C_TRADEBILL_PAYEEACCOUNT"].ToString();
                    model.D_TRADEBILL_STARTDATE = Convert.ToDateTime(dr["D_TRADEBILL_STARTDATE"].ToString());
                    model.D_TRADEBILL_ENDDATE = Convert.ToDateTime(dr["D_TRADEBILL_ENDDATE"].ToString());
                    model.N_TRADEBILL_INVOICEVALUE = Convert.ToDecimal(dr["N_TRADEBILL_INVOICEVALUE"].ToString());
                    model.C_TRADEBILL_CODE = dr["C_TRADEBILL_CODE"].ToString();
                    model.N_TRADEBILL_IFOFFER = Convert.ToDecimal(dr["N_TRADEBILL_IFOFFER"].ToString());
                    model.C_TRADEBILL_REMARK = dr["C_TRADEBILL_REMARK"].ToString();
                }
            }

            return model;
        }

        public int INSERT(YX_TradeBill model, string operName)
        {
            model.N_TRADEBILL_ID = GetSequence("SEQ_N_TRADEBILL_ID");
            string sql = string.Format(@" insert into yx_tradebill
   (n_tradebill_id,
    n_tradebill_drawercmyid,
    n_tradebill_drawerbankid,
    c_tradebill_draweraccount,
    n_tradebill_drawerid,
    c_tradebill_payername,
    c_tradebill_payeecmyname,
    c_tradebill_payeebankname,
    c_tradebill_payeeaccount,
    C_TRADEBILL_PAYEENAME,
    n_tradebill_invoicevalue,
    d_tradebill_startdate,
    d_tradebill_enddate,
    c_tradebill_code,
    n_tradebill_ifoffer,
    c_tradebill_remark)
 values
   ({0},
    {1},
    {2},
    '{3}',
    {4},
    '{5}',
    '{6}',
    '{7}',
    '{8}',
    '{9}',
    {10},
    TO_DATE('{11}', 'YYYY-MM-DD HH24:MI:SS'),
    TO_DATE('{12}', 'YYYY-MM-DD HH24:MI:SS'),
    '{13}',
    {14},
    '{15}')
", model.N_TRADEBILL_ID, model.N_TRADEBILL_DRAWERCMYID, model.N_TRADEBILL_DRAWERBANKID, model.C_TRADEBILL_DRAWERACCOUNT, model.N_TRADEBILL_DRAWERID, model.C_TRADEBILL_PAYERNAME, model.C_TRADEBILL_PAYEECMYNAME, model.C_TRADEBILL_PAYEEBANKNAME, model.C_TRADEBILL_PAYEEACCOUNT, model.C_TRADEBILL_PAYEENAME, model.N_TRADEBILL_INVOICEVALUE, model.D_TRADEBILL_STARTDATE, model.D_TRADEBILL_ENDDATE, model.C_TRADEBILL_CODE, model.N_TRADEBILL_IFOFFER, model.C_TRADEBILL_REMARK);

            int count = NoQuery(sql, "新增银行汇票", "INSERT", operName);
            return count;
        }

        public int UPDATE(YX_TradeBill model, string operName)
        {
            string sql = string.Format(@"update yx_tradebill
   set n_tradebill_drawercmyid   = {0},
       n_tradebill_drawerbankid  = {1},
       c_tradebill_draweraccount = '{2}',
       n_tradebill_drawerid      = {3},
       c_tradebill_payername     = '{4}',
       c_tradebill_payeecmyname  = '{5}',
       c_tradebill_payeebankname = '{6}',
       c_tradebill_payeeaccount  = '{7}',
       c_tradebill_payeename     = '{8}',
       n_tradebill_invoicevalue  = {9},
       d_tradebill_startdate     = TO_DATE('{10}', 'YYYY-MM-DD HH24:MI:SS'),
       d_tradebill_enddate       = TO_DATE('{11}', 'YYYY-MM-DD HH24:MI:SS'),
       c_tradebill_code          = '{12}',
       n_tradebill_ifoffer       = {13},
       c_tradebill_remark        = '{14}'
 where n_tradebill_id = {15}", model.N_TRADEBILL_DRAWERCMYID, model.N_TRADEBILL_DRAWERBANKID, model.C_TRADEBILL_DRAWERACCOUNT, model.N_TRADEBILL_DRAWERID, model.C_TRADEBILL_PAYERNAME, model.C_TRADEBILL_PAYEECMYNAME, model.C_TRADEBILL_PAYEEBANKNAME, model.C_TRADEBILL_PAYEEACCOUNT, model.C_TRADEBILL_PAYEENAME, model.N_TRADEBILL_INVOICEVALUE, model.D_TRADEBILL_STARTDATE, model.D_TRADEBILL_ENDDATE, model.C_TRADEBILL_CODE, model.N_TRADEBILL_IFOFFER, model.C_TRADEBILL_REMARK, model.N_TRADEBILL_ID);
            int count = NoQuery(sql, operName);
            return count;
        }
    }
}